using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;

namespace MyLib
{
    public class AccessDB
    {
        private static string ConnectString_Config = "";
        private static string mLastError = "";

        static AccessDB()
        {
            string password = "";
            password += "b";
            password += "o";
            password += "c";
            password += "a";
            password += "i";
            password += "b";
            password += "o";
            password += "c";
            password += "a";
            password += "i";
            ConnectString_Config = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Database Password={1}", MyLib.Utility.GetCurrentPath() + "config.mdb", password);
        }

        public static string GetLastError()
        {
            return mLastError;
        }

        protected static void SetLastError(string format, params object[] objects)
        {
            mLastError = String.Format(format, objects);
        }

        public static bool ReadTable(string connectstring, string sql, ref DataTable dt)
        {
            OleDbConnection conn = null;
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            try
            {
                conn = new OleDbConnection(connectstring);
                conn.Open();
                if (conn.State != ConnectionState.Open)
                {
                    SetLastError("Cannot connect to database.");
                    return false;
                }
                cmd = new OleDbCommand(sql, conn);
                reader = cmd.ExecuteReader();
                if (reader == null)
                    return false;

                dt = new DataTable();
                dt.Load(reader);
                return true;
            }
            catch (Exception ee)
            {
                SetLastError("Database operation failed. error={0}", ee.Message);
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Dispose();
                }
                if (cmd != null)
                    cmd.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (dt != null)
                    dt.Dispose();
            }
        }

        public static bool RunCommand(string connectstring, string sql)
        {
            OleDbConnection conn = null;
            OleDbCommand cmd = null;
            try
            {
                conn = new OleDbConnection(connectstring);
                conn.Open();
                if (conn.State != ConnectionState.Open)
                {
                    SetLastError("Cannot connect to database.");
                    return false;
                }
                cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ee)
            {
                SetLastError("Database operation failed. error={0}", ee.Message);
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Dispose();
                }
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public static string ReadConfig(string key, string def)
        {
            DataTable dt = null;
            string sql = String.Format("select * from settings where setting = '{0}'", key);
            if (!ReadTable(ConnectString_Config, sql, ref dt))
            {
                SetLastError("ReadConfig failed. {0}", GetLastError());
                return def;
            }
            if (dt.Rows.Count == 0)
                return def;
            return Convert.ToString(dt.Rows[0]["data"]);
        }

        public static int ReadConfig(string key, int def)
        {
            return Convert.ToInt32(ReadConfig(key, def.ToString()));
        }

        public static bool ReadConfig(string key, bool def)
        {
            string s = ReadConfig(key, (def ? "true" : "false")).ToLower();
            return ((s == "true") || (s == "t"));
        }

        public static bool WriteConfig(string key, string value)
        {
            string sql = "";
            sql = String.Format("delete from settings where setting = '{0}'", key);
            if (!RunCommand(ConnectString_Config, sql))
            {
                SetLastError("WriteConfig failed. {0}", GetLastError());
                return false;
            }
            sql = String.Format(" insert into settings (setting, data) values('{0}', '{1}')", key, value);
            if (!RunCommand(ConnectString_Config, sql))
            {
                SetLastError("WriteConfig failed. {0}", GetLastError());
                return false;
            }
            return true;
        }

        public static bool WriteConfig(string key, int value)
        {
            return WriteConfig(key, value.ToString());
        }

        public static bool WriteConfig(string key, bool value)
        {
            return WriteConfig(key, (value? "true" : "false"));
        }
    }
}
